package com.recommendation.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Logger;

import javax.naming.NamingException;

import com.recommendation.domain.RecommendItem;
import com.recommendation.domain.RecommendMode;
import com.recommendation.domain.RecommendScope;
import com.recommendation.domain.SpecialItems;

public class Dao {
	
	static Logger _logger = Logger.getLogger(Dao.class.getName());
	
	public static final Dao INSTANCE = new Dao();
	
	private Dao()
	{
		
	}
	
	public synchronized void updateUser(long uid, String nick, String session, short version) throws NamingException, SQLException
	{
		Connection conn = null;
		PreparedStatement statement = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "update user_t set session_c=?, version_c=?, last_login_c=? where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, session);
			statement.setShort(2, version);
			statement.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
			statement.setLong(4, uid);
			int result = statement.executeUpdate();
			if (result == 0)
			{
				sql = "insert into user_t(user_id_c, nick_c, session_c, version_c, last_login_c) values(?,?,?,?,?)";
				statement = conn.prepareStatement(sql);
				statement.setLong(1, uid);
				statement.setString(2, nick);
				statement.setString(3, session);
				statement.setShort(4, version);
				statement.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
			}
			statement.executeUpdate();
		}
		finally
		{
			DBUtils.close(conn, statement, null);
		}
	}
	
	public String getTopSession(long userId) throws NamingException, SQLException
	{
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "select session_c from user_t where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			rs = statement.executeQuery();
			if (rs.next())
			{
				return rs.getString("session_c");
			}
		}
		finally
		{
			DBUtils.close(conn, statement, rs);
		}
		return null;
	}
	
	public String getTopSession(String nick) throws NamingException, SQLException
	{
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "select session_c from user_t where nick_c=?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, nick);
			rs = statement.executeQuery();
			if (rs.next())
			{
				return rs.getString("session_c");
			}
		}
		finally
		{
			DBUtils.close(conn, statement, rs);
		}
		return null;
	}
	
	public short getVersion(long userId) throws NamingException, SQLException
	{
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "select version_c from user_t where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			rs = statement.executeQuery();
			if (rs.next())
			{
				return rs.getShort("version_c");
			}
		}
		finally
		{
			DBUtils.close(conn, statement, rs);
		}
		return -1;
	}
	
	public RecommendMode getRecommendMode(long userId) throws NamingException, SQLException
	{
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "select * from recommend_mode_t where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			rs = statement.executeQuery();
			if (rs.next())
			{
				RecommendMode mode = new RecommendMode();
				mode.setMode(rs.getShort("mode_c"));
				mode.setRecentPeriod(rs.getInt("recent_period_c"));
				return mode;
			}
		}
		finally
		{
			DBUtils.close(conn, statement, rs);
		}
		return null;
	}
	
	public synchronized void setRecommendMode(RecommendMode mode, long userId, Connection conn) throws SQLException
	{
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			String sql = "select user_id_c from recommend_mode_t where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			rs = statement.executeQuery();
			boolean exists = rs.next();
			if (exists)
			{
				sql = "update recommend_mode_t set mode_c=?, recent_period_c=? where user_id_c=?";
				statement = conn.prepareStatement(sql);
				statement.setShort(1, mode.getMode());
				statement.setInt(2, mode.getRecentPeriod());
				statement.setLong(3, userId);
				statement.executeUpdate();
			}
			else
			{
				sql = "insert into recommend_mode_t(user_id_c, mode_c, recent_period_c) values(?,?,?)";
				statement = conn.prepareStatement(sql);
				statement.setLong(1, userId);
				statement.setShort(2, mode.getMode());
				statement.setInt(3, mode.getRecentPeriod());
				statement.executeUpdate();
			}			
		}
		finally
		{
			DBUtils.close(null, statement, rs);
		}
	}
	
	public RecommendScope getRecommendScope(long userId) throws NamingException, SQLException
	{
		RecommendScope scope = null;
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "select * from recommend_scope_t where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			rs = statement.executeQuery();
			if (rs.next())
			{
				scope = new RecommendScope();
				scope.setType(rs.getShort("type_c"));
				scope.setKeyword(rs.getString("keyword_c"));
				scope.setSellerCids(rs.getString("categories_c"));
				scope.setItems(rs.getString("items_c"));
			}
		}
		finally
		{
			DBUtils.close(conn, statement, rs);
		}
		return scope;
	}
	
	public synchronized void setRecommendScope(RecommendScope scope, long userId, Connection conn) throws SQLException
	{
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			String sql = "select user_id_c from recommend_scope_t where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			rs = statement.executeQuery();
			boolean exists = rs.next();
			if (exists)
			{
				
				sql = "update recommend_scope_t set type_c=?, keyword_c=?, categories_c=?, items_c=? where user_id_c=?";
				statement = conn.prepareStatement(sql);
				statement.setShort(1, scope.getType());
				statement.setString(2, scope.getKeyword());
				statement.setString(3, scope.getSellerCids());
				statement.setString(4, scope.getItems());
				statement.setLong(5, userId);
				statement.executeUpdate();
			}
			else
			{
				sql = "insert into recommend_scope_t(user_id_c, type_c, keyword_c, categories_c, items_c) values(?,?,?,?,?)";
				statement = conn.prepareStatement(sql);
				statement.setLong(1, userId);
				statement.setShort(2, scope.getType());
				statement.setString(3, scope.getKeyword());
				statement.setString(4, scope.getSellerCids());
				statement.setString(5, scope.getItems());
				statement.executeUpdate();
			}			
		}
		finally
		{
			DBUtils.close(null, statement, rs);
		}
	}
	
	public SpecialItems getSpecialItems(long userId) throws NamingException, SQLException
	{
		SpecialItems special = null;
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "select * from special_item_t where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			rs = statement.executeQuery();
			if (rs.next())
			{
				special = new SpecialItems();
				special.setMustItemsEnabled(rs.getBoolean("must_enabled_c"));
				special.setMustItems(rs.getString("must_c"));
				special.setNotItemsEnabled(rs.getBoolean("not_enabled_c"));
				special.setNotItems(rs.getString("not_c"));
				special.setNotKeyword(rs.getString("not_keyword_c"));
			}
		}
		finally
		{
			DBUtils.close(conn, statement, rs);
		}
		return special;
	}
	
	public synchronized void setSpecialItems(SpecialItems special, long userId, Connection conn) throws SQLException
	{
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			String sql = "select user_id_c from special_item_t where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			rs = statement.executeQuery();
			boolean exists = rs.next();
			if (exists)
			{
				
				sql = "update special_item_t set must_enabled_c=?, must_c=?, not_enabled_c=?, not_c=?, not_keyword_c=? where user_id_c=?";
				statement = conn.prepareStatement(sql);
				statement.setBoolean(1, special.isMustItemsEnabled());
				statement.setString(2, special.getMustItems());
				statement.setBoolean(3, special.isNotItemsEnabled());
				statement.setString(4, special.getNotItems());
				statement.setString(5, special.getNotKeyword());
				statement.setLong(6, userId);
				statement.executeUpdate();
			}
			else
			{
				sql = "insert into special_item_t(user_id_c, must_enabled_c, must_c, not_enabled_c, not_c, not_keyword_c) values(?,?,?,?,?,?)";
				statement = conn.prepareStatement(sql);
				statement.setLong(1, userId);
				statement.setBoolean(2, special.isMustItemsEnabled());
				statement.setString(3, special.getMustItems());
				statement.setBoolean(4, special.isNotItemsEnabled());
				statement.setString(5, special.getNotItems());
				statement.setString(6, special.getNotKeyword());
				statement.executeUpdate();
			}			
		}
		finally
		{
			DBUtils.close(null, statement, rs);
		}
	}
	
	public synchronized void addRecentRecommendedItem(long numIid, String title, String picUrl, Date delistTime, String msg, long userId, Connection conn) throws SQLException
	{
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			String sql = "select num_iid_c from recent_recommended_item_t where num_iid_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, numIid);
			rs = statement.executeQuery();
			boolean exists = rs.next();
			if (exists)
			{
				sql = "update recent_recommended_item_t set delist_time_c=?, time_c=?, msg_c=? where num_iid_c=?";
				statement = conn.prepareStatement(sql);
				Timestamp delistTimeStamp = new Timestamp(delistTime.getTime());
				statement.setTimestamp(1, delistTimeStamp);
				Timestamp time = new Timestamp(System.currentTimeMillis());
				statement.setTimestamp(2, time);
				statement.setString(3, msg);
				statement.setLong(4, numIid);
				statement.executeUpdate();
			}
			else
			{
				sql = "insert into recent_recommended_item_t(num_iid_c,title_c,pic_url_c,delist_time_c,time_c,msg_c,user_id_c) values(?,?,?,?,?,?,?)";
				statement = conn.prepareStatement(sql);
				statement.setLong(1, numIid);
				statement.setString(2, title);
				statement.setString(3, picUrl);
				Timestamp delistTimeStamp = new Timestamp(delistTime.getTime());
				statement.setTimestamp(4, delistTimeStamp);
				Timestamp time = new Timestamp(System.currentTimeMillis());
				statement.setTimestamp(5, time);
				statement.setString(6, msg);
				statement.setLong(7, userId);
				statement.executeUpdate();
			}			
		}
		finally
		{
			DBUtils.close(null, statement, rs);
		}
	}
	
	public List<RecommendItem> getRecentRecommendedItems(long userId, Connection conn) throws NamingException, SQLException
	{
		List<RecommendItem> items = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			String sql = "select * from recent_recommended_item_t where user_id_c=? order by time_c desc";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			rs = statement.executeQuery();
			items = new ArrayList<RecommendItem>();
			while (rs.next())
			{
				RecommendItem item = new RecommendItem();
				item.setNumIid(rs.getLong("num_iid_c"));
				item.setTitle(rs.getString("title_c"));
				item.setPicUrl(rs.getString("pic_url_c"));
				item.setDelistTime(rs.getTimestamp("delist_time_c"));
				item.setRecommendTime(rs.getTimestamp("time_c"));
				item.setMsg(rs.getString("msg_c"));
				items.add(item);
			}
		}
		finally
		{
			DBUtils.close(null, statement, rs);
		}
		return items;
	}
	
	public List<Long> getOldestRecommendedItem(long userId, int limit, Connection conn) throws NamingException, SQLException
	{
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			String sql = "select num_iid_c from recent_recommended_item_t where user_id_c=? order by time_c asc limit ? offset 0";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			statement.setInt(2, limit);
			rs = statement.executeQuery();
			List<Long> numIids = new ArrayList<Long>();
			while (rs.next())
			{
				numIids.add(rs.getLong("num_iid_c"));
			}
			return numIids;
		}
		finally
		{
			DBUtils.close(null, statement, rs);
		}
	}
	
	public int getRecentRecommendedItemsCount(long userId, Connection conn) throws NamingException, SQLException
	{
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			String sql = "select count(num_iid_c) c from recent_recommended_item_t where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			rs = statement.executeQuery();
			if (rs.next())
			{
				return rs.getInt("c");
			}
		}
		finally
		{
			DBUtils.close(null, statement, rs);
		}
		return 0;
	}
	
	public synchronized void deleteRecentRecommendedItems(List<Long> numIids, Connection conn) throws NamingException, SQLException
	{
		PreparedStatement statement = null;
		try
		{
			String sql = "delete from recent_recommended_item_t where num_iid_c=?";
			statement = conn.prepareStatement(sql);
			for(Long numIid : numIids)
			{
				statement.setLong(1, numIid);
				statement.addBatch();
			}
			statement.executeBatch();
		}
		finally
		{
			DBUtils.close(null, statement, null);
		}
	}
}
